【新機能】Redshift ALTER TABLE APPENDによるデータ移動を試してみました

#Amazon Redshift

#AWS

石川覚

2016.02.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

ALTER TABLE APPENDによるデータ移動が利用できるようになりました。（Cluster Version: 1.0.1034）他のRDBでは聞いたことのない "ALTER TABLE APPEND" ですが、この機能はデータの物理的な移動を行わずにテーブル間でのデータ移動を実現することで高速になるというメリットがあります。どういった用途に適した機能であるかご紹介します。

ALTER TABLE APPENDによるデータ移動が追加 (2016/02/09)

ALTER TABLE APPEND とは

この新しいALTER TABLE APPENDは、ある表（ソーステーブル）のデータ全体を別の表（ターゲットテーブル）にデータを「移動（MOVE）」する機能です。「移動」ですので、ソーステーブルは空（から）になります。つまり、従来はINSERT INTO SELECT と、ソーステーブルのデータ削除（DELETE or TRUNCATE）が必要でしたが、ALTER TABLE APPEND ではデータの物理的な移動を行わずにテーブル間のデータ移動を実現しています。そのため実行速度が高速になる一方でソーステーブルからはデータが消えるというわけです。

詳細は、Redshift公式マニュアル（英語）ALTER TABLE APPEND を参照してください。

ALTER TABLE APPEND の構文

ALTER TABLE target_table_name APPEND FROM source_table_name 
[ IGNOREEXTRA | FILLTARGET ]

ALTER TABLE APPEND の使用上の注意

ソーステーブルとターゲットテーブルのカラム定義に違いがあった場合はどうなるのでしょうか？この場合は ALTER TABLE APPENDにIGNOREEXTRA か FILLTARGET のどちらかのオプションを指定する必要があります。

IGNOREEXTRA：ソーステーブルにある列がターゲットテーブルに存在しない場合、その列は無視されます（捨てられます）
FILLTARGET：ターゲットテーブルに、ソースには無い列が存在する場合、その列にはデフォルト値が適用されます。デフォルトが定義されていないケースではNULLが入ります
ソーステーブルとターゲットテーブルの両方が余分な列が含まれている場合、コマンドは失敗します。FILLTARGETとIGNOREEXTRAの両方を使用することはできません。

なお、ALTER TABLE APPEND はDDLであり、トランザクション対応では無いのでROLLBACKできません。

同じ名前で異なる属性を持つカラムが両方のテーブルに存在する場合、コマンドは失敗します。同じ名前の列は、以下の共通の属性を持っている必要があります。

Data type
Column size
Compression encoding
Not null
Sort style
Sort key columns
Distribution style
Distribution key columns

実行例

以下の例では、sales_monthly （ソーステーブル）のデータ全体をsales（ターゲットテーブル）にデータを移動します。

alter table sales append from sales_monthly;

動作検証

ALTER TABLE APPEND は内部的にソーステーブルのデータブロックをターゲットテーブルに移動していると考えられます。

検証の条件

テーブル定義

ソーステーブルとターゲットテーブル定義は同じ、ソートキーは lo_orderdate とします。

CREATE TABLE public.lineorder_target 
(
  lo_orderkey          INTEGER NOT NULL,
  lo_linenumber        INTEGER NOT NULL,
  lo_custkey           INTEGER NOT NULL,
  lo_partkey           INTEGER NOT NULL,
  lo_suppkey           INTEGER NOT NULL,
  lo_orderdate         INTEGER NOT NULL,
  lo_orderpriority     VARCHAR(15) NOT NULL,
  lo_shippriority      VARCHAR(1) NOT NULL,
  lo_quantity          INTEGER NOT NULL,
  lo_extendedprice     INTEGER NOT NULL,
  lo_ordertotalprice   INTEGER NOT NULL,
  lo_discount          INTEGER NOT NULL,
  lo_revenue           INTEGER NOT NULL,
  lo_supplycost        INTEGER NOT NULL,
  lo_tax               INTEGER NOT NULL,
  lo_commitdate        INTEGER NOT NULL,
  lo_shipmode          VARCHAR(10) NOT NULL
)
distkey(lo_partkey)
sortkey(lo_orderdate);

テスト用データ

db=> INSERT INTO public.lineorder_1992 SELECT * FROM public.lineorder WHERE lo_orderdate BETWEEN 19920000 AND 19929999;
INSERT 0 11402480
Time: 12061.933 ms
db=> INSERT INTO public.lineorder_1993 SELECT * FROM public.lineorder WHERE lo_orderdate BETWEEN 19930000 AND 19939999;
INSERT 0 11379006
Time: 12843.286 ms

[従来] ソート済みデータをディープコピー（INSERT INTO SELECT）

空のテーブルにソート済みデータをディープコピー

INSERT INTO SELECT でソート済みデータをディープコピーすると、ターゲットテーブル(lineorder_target)のデータはソート済みで格納されますので、VACUUMは不要です。ANALYZEの実行と、ソーステーブルのTRUNCATEを実施します。データのディープコピーには12.878秒、データのソートは不要、統計情報の更新は3.806秒、ソーステーブルのデータ削除は0.423秒で、全て含めると17.107秒でした。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 0_not_sorted | lineorder_target   |           0 |        0 |           0.000
 1_sorted     | lineorder_1992     |    11402480 | 11402480 |           1.000

db=> INSERT INTO public.lineorder_target SELECT * FROM public.lineorder_1992;
INSERT 0 11402480
Time: 12878.085 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 1_sorted     | lineorder_1992     |    11402480 | 11402480 |           1.000

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 3806.338 ms

db=> TRUNCATE TABLE public.lineorder_1992;
TRUNCATE TABLE and COMMIT TRANSACTION
Time: 423.389 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000

更にテーブルにソート済みデータをディープコピーで追加

ターゲットテーブル(lineorder_target)へ更にINSERT INTO SELECT でソート済みデータをディープコピーします。ターゲットテーブル(lineorder_target)のデータは未ソートで格納されますので、VACUUMは必要です。ANALYZEの実行と、ソーステーブルのTRUNCATEを実施します。データのディープコピーには12.717秒、データのソートは28.269秒、統計情報の更新は2.108秒、ソーステーブルのデータ削除は0.879秒で、全て含めると43.973秒でした。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000 

db=> INSERT INTO public.lineorder_target SELECT * FROM public.lineorder_1993;
INSERT 0 11379006
Time: 12717.126 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 22781486 |           0.501
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000

db=> VACUUM public.lineorder_target;
VACUUM
Time: 28269.610 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    22781486 | 22781486 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 2108.294 ms

db=> TRUNCATE TABLE public.lineorder_1993;
TRUNCATE TABLE and COMMIT TRANSACTION
Time: 879.741 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    22781486 | 22781486 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 0_not_sorted | lineorder_1993     |           0 |        0 |           0.000

[新] ソート済みデータを移動（ALTER TABLE APPEND）

空のテーブルにソート済みデータを移動

ALTER TABLE APPEND でソート済みデータを移動すると、ターゲットテーブル(lineorder_target)のデータは未ソートで格納されますので、VACUUMが必要です。ANALYZEの実行を実施します。データは移動するのでソーステーブルのデータ削除は不要です。データの移動には1.211秒でしたが、データのソートは47.653秒、統計情報の更新は1.021秒で、全て含めると49.855秒でした。データの移動は高速ですが、データのソート（VACUUM）はかなり時間がかかってしまいました。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 0_not_sorted | lineorder_target   |           0 |        0 |           0.000
 1_sorted     | lineorder_1992     |    11402480 | 11402480 |           1.000

db=> ALTER TABLE public.lineorder_target APPEND FROM public.lineorder_1992;
INFO:  ALTER TABLE APPEND "lineorder_target" from "lineorder_1992" is complete.
ALTER TABLE APPEND and COMMIT TRANSACTION
Time: 1211.296 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 0_not_sorted | lineorder_target   |           0 | 11402480 |           0.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000

db=> VACUUM public.lineorder_target;
VACUUM
Time: 47653.109 ms

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 1021.992 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000

更にテーブルにソート済みデータを移動

ターゲットテーブル(lineorder_target)へ更にALTER TABLE APPEND でソート済みデータを移動します。先程と同様にターゲットテーブル(lineorder_target)のデータは未ソートで格納されますので、VACUUMが必要です。ANALYZEの実行を実施します。データは移動するのでソーステーブルのデータ削除は不要です。データの移動には1.344秒でしたが、データのソートは27.653秒、統計情報の更新は1.602秒で、全て含めると30.589秒でした。データのソート（VACUUM）は空のテーブルに移動させた後のVACUUMの半分の時間で済ました。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000 

db=> ALTER TABLE public.lineorder_target APPEND FROM public.lineorder_1993;
INFO:  ALTER TABLE APPEND "lineorder_target" from "lineorder_1993" is complete.
ALTER TABLE APPEND and COMMIT TRANSACTION
Time: 1344.405 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 22781486 |           0.501
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 0_not_sorted | lineorder_1993     |           0 |        0 |           0.000

db=> VACUUM public.lineorder_target;
VACUUM
Time: 27653.198 ms

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 1602.358 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    22781486 | 22781486 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 0_not_sorted | lineorder_1993     |           0 |        0 |           0.000

検証の結果

検証結果を以下の表にまとめました。ALTER TABLE APPENDは、データの「移動」については速いのですが、ソート済みデータを移動しても未ソートデータとして格納されるため、ソートキー指定したテーブルではデータ移動後にVACUUMが必要となります。

[従来] ソート済みデータをディープコピー（INSERT INTO SELECT）

データ	ディープコピー時間	VACUUM時間	ANALYZE時間	TRUNCATE時間	合計時間
1992	12.878秒	(初回は不要)	3.806秒	0.423秒	17.107秒
1993	12.717秒	28.269秒	2.108秒	0.879秒	43.973秒

[新] ソート済みデータを移動（ALTER TABLE APPEND）

データ	データ移動時間	VACUUM時間	ANALYZE時間	TRUNCATE時間	合計時間
1992	1.211秒	47.653秒	1.021秒	(不要)	49.855秒
1993	1.344秒	27.653秒	1.602秒	(不要)	30.589秒

最後に

ALTER TABLE APPENDでソート済みデータを移動することで、データブロックやゾーンマップのマージができたら画期的だと思いましたが、残念ながら現状は「移動」のようです。といっても検証の結果から、ディープコピー（INSERT INTO SELECT）とデータの移動（ALTER TABLE APPEND）を組合せることでデータコピーが約３割ほど速くなることがわかりました。

以下の方針に従い、データコピーの方式の見直しをご検討ください。